package com.xdb.util;

import com.xdb.logging.Logger;
import com.xdb.logging.LogFactory;

import javax.sql.DataSource;

import java.io.Closeable;
import java.io.InputStream;
import java.io.PrintStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Enumeration;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public final class JdbcUtils implements JDBCConstants {

	private final static Logger LOG = LogFactory.getLog(JdbcUtils.class);

	private static final Properties DRIVER_URL_MAPPING = new Properties();

	static {
		try {
			ClassLoader ctxClassLoader = Thread.currentThread().getContextClassLoader();
			if (ctxClassLoader != null) {
				for (Enumeration<URL> e = ctxClassLoader.getResources("META-INF/druid-driver.properties"); e.hasMoreElements();) {
					URL url = e.nextElement();

					Properties property = new Properties();

					InputStream is = null;
					try {
						is = url.openStream();
						property.load(is);
					} finally {
						JdbcUtils.close(is);
					}

					DRIVER_URL_MAPPING.putAll(property);
				}
			}
		} catch (Exception e) {
			LOG.error("load druid-driver.properties error", e);
		}
	}

	public static void close(Connection x) {
		if (x == null) {
			return;
		}
		try {
			x.close();
		} catch (Exception e) {
			LOG.debug("close connection error", e);
		}
	}

	public static void close(Statement x) {
		if (x == null) {
			return;
		}
		try {
			x.close();
		} catch (Exception e) {
			LOG.debug("close statement error", e);
		}
	}

	public static void close(ResultSet x) {
		if (x == null) {
			return;
		}
		try {
			x.close();
		} catch (Exception e) {
			LOG.debug("close result set error", e);
		}
	}

	public static void close(Closeable x) {
		if (x == null) {
			return;
		}

		try {
			x.close();
		} catch (Exception e) {
			LOG.debug("close error", e);
		}
	}

	public static void printResultSet(ResultSet rs) throws SQLException {
		printResultSet(rs, System.out);
	}

	public static void printResultSet(ResultSet rs, PrintStream out) throws SQLException {
		printResultSet(rs, out, true, "\t");
	}

	public static void printResultSet(ResultSet rs, PrintStream out, boolean printHeader, String seperator) throws SQLException {
		ResultSetMetaData metadata = rs.getMetaData();
		int columnCount = metadata.getColumnCount();
		if (printHeader) {
			for (int columnIndex = 1; columnIndex <= columnCount; ++columnIndex) {
				if (columnIndex != 1) {
					out.print(seperator);
				}
				out.print(metadata.getColumnName(columnIndex));
			}
		}

		out.println();

		while (rs.next()) {

			for (int columnIndex = 1; columnIndex <= columnCount; ++columnIndex) {
				if (columnIndex != 1) {
					out.print(seperator);
				}

				int type = metadata.getColumnType(columnIndex);

				if (type == Types.VARCHAR || type == Types.CHAR || type == Types.NVARCHAR || type == Types.NCHAR) {
					out.print(rs.getString(columnIndex));
				} else if (type == Types.DATE) {
					Date date = rs.getDate(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(date.toString());
					}
				} else if (type == Types.BIT) {
					boolean value = rs.getBoolean(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(Boolean.toString(value));
					}
				} else if (type == Types.BOOLEAN) {
					boolean value = rs.getBoolean(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(Boolean.toString(value));
					}
				} else if (type == Types.TINYINT) {
					byte value = rs.getByte(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(Byte.toString(value));
					}
				} else if (type == Types.SMALLINT) {
					short value = rs.getShort(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(Short.toString(value));
					}
				} else if (type == Types.INTEGER) {
					int value = rs.getInt(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(Integer.toString(value));
					}
				} else if (type == Types.BIGINT) {
					long value = rs.getLong(columnIndex);
					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(Long.toString(value));
					}
				} else if (type == Types.TIMESTAMP) {
					out.print(String.valueOf(rs.getTimestamp(columnIndex)));
				} else if (type == Types.DECIMAL) {
					out.print(String.valueOf(rs.getBigDecimal(columnIndex)));
				} else if (type == Types.CLOB) {
					out.print(String.valueOf(rs.getString(columnIndex)));
				} else if (type == Types.JAVA_OBJECT) {
					Object object = rs.getObject(columnIndex);

					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(String.valueOf(object));
					}
				} else if (type == Types.LONGVARCHAR) {
					Object object = rs.getString(columnIndex);

					if (rs.wasNull()) {
						out.print("null");
					} else {
						out.print(String.valueOf(object));
					}
				} else if (type == Types.NULL) {
					out.print("null");
				} else {
					Object object = rs.getObject(columnIndex);

					if (rs.wasNull()) {
						out.print("null");
					} else {
						if (object instanceof byte[]) {
							byte[] bytes = (byte[]) object;
							String text = Hex.encode(bytes);
							out.print(text);
						} else {
							out.print(String.valueOf(object));
						}
					}
				}
			}
			out.println();
		}
	}

	public static String getTypeName(int sqlType) {
		switch (sqlType) {
		case Types.ARRAY:
			return "ARRAY";

		case Types.BIGINT:
			return "BIGINT";

		case Types.BINARY:
			return "BINARY";

		case Types.BIT:
			return "BIT";

		case Types.BLOB:
			return "BLOB";

		case Types.BOOLEAN:
			return "BOOLEAN";

		case Types.CHAR:
			return "CHAR";

		case Types.CLOB:
			return "CLOB";

		case Types.DATALINK:
			return "DATALINK";

		case Types.DATE:
			return "DATE";

		case Types.DECIMAL:
			return "DECIMAL";

		case Types.DISTINCT:
			return "DISTINCT";

		case Types.DOUBLE:
			return "DOUBLE";

		case Types.FLOAT:
			return "FLOAT";

		case Types.INTEGER:
			return "INTEGER";

		case Types.JAVA_OBJECT:
			return "JAVA_OBJECT";

		case Types.LONGNVARCHAR:
			return "LONGNVARCHAR";

		case Types.LONGVARBINARY:
			return "LONGVARBINARY";

		case Types.NCHAR:
			return "NCHAR";

		case Types.NCLOB:
			return "NCLOB";

		case Types.NULL:
			return "NULL";

		case Types.NUMERIC:
			return "NUMERIC";

		case Types.NVARCHAR:
			return "NVARCHAR";

		case Types.REAL:
			return "REAL";

		case Types.REF:
			return "REF";

		case Types.ROWID:
			return "ROWID";

		case Types.SMALLINT:
			return "SMALLINT";

		case Types.SQLXML:
			return "SQLXML";

		case Types.STRUCT:
			return "STRUCT";

		case Types.TIME:
			return "TIME";

		case Types.TIMESTAMP:
			return "TIMESTAMP";

		case Types.TINYINT:
			return "TINYINT";

		case Types.VARBINARY:
			return "VARBINARY";

		case Types.VARCHAR:
			return "VARCHAR";

		default:
			return "OTHER";

		}
	}

	public static String getDriverClassName(String rawUrl) throws SQLException {
		if (rawUrl.startsWith("jdbc:derby:")) {
			return "org.apache.derby.jdbc.EmbeddedDriver";
		} else if (rawUrl.startsWith("jdbc:mysql:")) {
			return MYSQL_DRIVER;
		} else if (rawUrl.startsWith("jdbc:oracle:") //
				|| rawUrl.startsWith("JDBC:oracle:")) {
			return ORACLE_DRIVER;
		} else if (rawUrl.startsWith("jdbc:microsoft:")) {
			return "com.microsoft.jdbc.sqlserver.SQLServerDriver";
		} else if (rawUrl.startsWith("jdbc:sqlserver:")) {
			return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		} else if (rawUrl.startsWith("jdbc:sybase:Tds:")) {
			return "com.sybase.jdbc2.jdbc.SybDriver";
		} else if (rawUrl.startsWith("jdbc:jtds:")) {
			return "net.sourceforge.jtds.jdbc.Driver";
		} else if (rawUrl.startsWith("jdbc:fake:") || rawUrl.startsWith("jdbc:mock:")) {
			return "com.alibaba.druid.mock.MockDriver";
		} else if (rawUrl.startsWith("jdbc:postgresql:")) {
			return "org.postgresql.Driver";
		} else if (rawUrl.startsWith("jdbc:hsqldb:")) {
			return "org.hsqldb.jdbcDriver";
		} else if (rawUrl.startsWith("jdbc:sqlite:")) {
			return "org.sqlite.JDBC";
		} else if (rawUrl.startsWith("jdbc:ingres:")) {
			return "com.ingres.jdbc.IngresDriver";
		} else if (rawUrl.startsWith("jdbc:h2:")) {
			return H2_DRIVER;
		} else if (rawUrl.startsWith("jdbc:mckoi:")) {
			return "com.mckoi.JDBCDriver";
		} else if (rawUrl.startsWith("jdbc:cloudscape:")) {
			return "COM.cloudscape.core.JDBCDriver";
		} else if (rawUrl.startsWith("jdbc:informix-sqli:")) {
			return "com.informix.jdbc.IfxDriver";
		} else if (rawUrl.startsWith("jdbc:timesten:")) {
			return "com.timesten.jdbc.TimesTenDriver";
		} else if (rawUrl.startsWith("jdbc:as400:")) {
			return "com.ibm.as400.access.AS400JDBCDriver";
		} else if (rawUrl.startsWith("jdbc:sapdb:")) {
			return "com.sap.dbtech.jdbc.DriverSapDB";
		} else if (rawUrl.startsWith("jdbc:JSQLConnect:")) {
			return "com.jnetdirect.jsql.JSQLDriver";
		} else if (rawUrl.startsWith("jdbc:JTurbo:")) {
			return "com.newatlanta.jturbo.driver.Driver";
		} else if (rawUrl.startsWith("jdbc:firebirdsql:")) {
			return "org.firebirdsql.jdbc.FBDriver";
		} else if (rawUrl.startsWith("jdbc:interbase:")) {
			return "interbase.interclient.Driver";
		} else if (rawUrl.startsWith("jdbc:pointbase:")) {
			return "com.pointbase.jdbc.jdbcUniversalDriver";
		} else if (rawUrl.startsWith("jdbc:edbc:")) {
			return "ca.edbc.jdbc.EdbcDriver";
		} else if (rawUrl.startsWith("jdbc:mimer:multi1:")) {
			return "com.mimer.jdbc.Driver";
		} else {
			throw new SQLException("unkow jdbc driver : " + rawUrl);
		}
	}

	public static String getDbType(String rawUrl, String driverClassName) {
		if (rawUrl == null) {
			return null;
		}

		if (rawUrl.startsWith("jdbc:mysql:") || rawUrl.startsWith("jdbc:cobar:") || rawUrl.startsWith("jdbc:log4jdbc:mysql:")) {
			return MYSQL;
		} else if (rawUrl.startsWith("jdbc:oracle:") || rawUrl.startsWith("jdbc:log4jdbc:oracle:")) {
			return ORACLE;
		} else if (rawUrl.startsWith("jdbc:jtds:") || rawUrl.startsWith("jdbc:log4jdbc:jtds:")) {
			return JTDS;
		} else if (rawUrl.startsWith("jdbc:sqlite:")) {
			return "sqlite";
		} else if (rawUrl.startsWith("jdbc:ingres:")) {
			return "ingres";
		} else if (rawUrl.startsWith("jdbc:h2:") || rawUrl.startsWith("jdbc:log4jdbc:h2:")) {
			return H2;
		} else if (rawUrl.startsWith("jdbc:mckoi:")) {
			return "mckoi";
		} else if (rawUrl.startsWith("jdbc:cloudscape:")) {
			return "cloudscape";
		} else if (rawUrl.startsWith("jdbc:informix-sqli:") || rawUrl.startsWith("jdbc:log4jdbc:informix-sqli:")) {
			return "informix";
		} else if (rawUrl.startsWith("jdbc:timesten:")) {
			return "timesten";
		} else if (rawUrl.startsWith("jdbc:as400:")) {
			return "as400";
		} else if (rawUrl.startsWith("jdbc:sapdb:")) {
			return "sapdb";
		} else if (rawUrl.startsWith("jdbc:JSQLConnect:")) {
			return "JSQLConnect";
		} else if (rawUrl.startsWith("jdbc:JTurbo:")) {
			return "JTurbo";
		} else if (rawUrl.startsWith("jdbc:firebirdsql:")) {
			return "firebirdsql";
		} else if (rawUrl.startsWith("jdbc:interbase:")) {
			return "interbase";
		} else if (rawUrl.startsWith("jdbc:pointbase:")) {
			return "pointbase";
		} else if (rawUrl.startsWith("jdbc:edbc:")) {
			return "edbc";
		} else if (rawUrl.startsWith("jdbc:mimer:multi1:")) {
			return "mimer";
		} else {
			return null;
		}
	}

	public static Driver createDriver(String driverClassName) throws SQLException {
		return createDriver(null, driverClassName);
	}

	public static Driver createDriver(ClassLoader classLoader, String driverClassName) throws SQLException {
		Class<?> clazz = null;
		if (classLoader != null) {
			try {
				clazz = classLoader.loadClass(driverClassName);
			} catch (ClassNotFoundException e) {
				// skip
			}
		}

		if (clazz == null) {
			try {
				ClassLoader contextLoader = Thread.currentThread().getContextClassLoader();
				if (contextLoader != null) {
					clazz = contextLoader.loadClass(driverClassName);
				}
			} catch (ClassNotFoundException e) {
				// skip
			}
		}

		if (clazz == null) {
			try {
				clazz = Class.forName(driverClassName);
			} catch (ClassNotFoundException e) {
				throw new SQLException(e.getMessage(), e);
			}
		}

		try {
			return (Driver) clazz.newInstance();
		} catch (IllegalAccessException e) {
			throw new SQLException(e.getMessage(), e);
		} catch (InstantiationException e) {
			throw new SQLException(e.getMessage(), e);
		}
	}

	public static int executeUpdate(DataSource dataSource, String sql, Object... parameters) throws SQLException {
		return executeUpdate(dataSource, sql, Arrays.asList(parameters));
	}

	public static int executeUpdate(DataSource dataSource, String sql, List<Object> parameters) throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			return executeUpdate(conn, sql, parameters);
		} finally {
			close(conn);
		}
	}

	public static int executeUpdate(Connection conn, String sql, List<Object> parameters) throws SQLException {
		PreparedStatement stmt = null;

		int updateCount;
		try {
			stmt = conn.prepareStatement(sql);

			setParameters(stmt, parameters);

			updateCount = stmt.executeUpdate();
		} finally {
			JdbcUtils.close(stmt);
		}

		return updateCount;
	}

	public static void execute(DataSource dataSource, String sql, Object... parameters) throws SQLException {
		execute(dataSource, sql, Arrays.asList(parameters));
	}

	public static void execute(DataSource dataSource, String sql, List<Object> parameters) throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			execute(conn, sql, parameters);
		} finally {
			close(conn);
		}
	}

	public static void execute(Connection conn, String sql, List<Object> parameters) throws SQLException {
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);

			setParameters(stmt, parameters);

			stmt.executeUpdate();
		} finally {
			JdbcUtils.close(stmt);
		}
	}

	public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, Object... parameters) throws SQLException {
		return executeQuery(dataSource, sql, Arrays.asList(parameters));
	}

	public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, List<Object> parameters) throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			return executeQuery(conn, sql, parameters);
		} finally {
			close(conn);
		}
	}

	public static List<Map<String, Object>> executeQuery(Connection conn, String sql, List<Object> parameters) throws SQLException {
		List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(sql);

			setParameters(stmt, parameters);

			rs = stmt.executeQuery();

			ResultSetMetaData rsMeta = rs.getMetaData();

			while (rs.next()) {
				Map<String, Object> row = new LinkedHashMap<String, Object>();

				for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) {
					String columName = rsMeta.getColumnLabel(i + 1);
					Object value = rs.getObject(i + 1);
					row.put(columName, value);
				}

				rows.add(row);
			}
		} finally {
			JdbcUtils.close(rs);
			JdbcUtils.close(stmt);
		}

		return rows;
	}

	private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
		for (int i = 0, size = parameters.size(); i < size; ++i) {
			Object param = parameters.get(i);
			stmt.setObject(i + 1, param);
		}
	}

	public static void insertToTable(DataSource dataSource, String tableName, Map<String, Object> data) throws SQLException {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			insertToTable(conn, tableName, data);
		} finally {
			close(conn);
		}
	}

	public static void insertToTable(Connection conn, String tableName, Map<String, Object> data) throws SQLException {
		String sql = makeInsertToTableSql(tableName, data.keySet());
		List<Object> parameters = new ArrayList<Object>(data.values());
		execute(conn, sql, parameters);
	}

	public static String makeInsertToTableSql(String tableName, Collection<String> names) {
		StringBuilder sql = new StringBuilder() //
				.append("insert into ") //
				.append(tableName) //
				.append("("); //

		int nameCount = 0;
		for (String name : names) {
			if (nameCount > 0) {
				sql.append(",");
			}
			sql.append(name);
			nameCount++;
		}
		sql.append(") values (");
		for (int i = 0; i < nameCount; ++i) {
			if (i != 0) {
				sql.append(",");
			}
			sql.append("?");
		}
		sql.append(")");

		return sql.toString();
	}
}
